#!/use/bin/python
# coding=utf-8
# 日志项额外操作
import datetime
import pymysql

from dbutils.pooled_db import PooledDB



# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.150.109', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

def updateLogUtils(selectSql,updateSql,whereInCloumn,logDetail):
    try:
        connData, curData = getDataConnection()
        connConf, curConf = getConfConnection()
        # 查询日志项
        #selectSql = f"""select t.active_id from log_common_service_{yyMMddDate} t where t.key ='{logName}' limit 5000 """
        curData.execute(selectSql)
        selectResult = curData.fetchall()
        #updateSql = "update life_assistant_devices t set  switches_state1 = switches_state1 |(1 << (12 - 1)) where 1=1"
        updateNum = 0
        whereStr = ""
        for result in selectResult:
            id = result[0]
            whereStr = f"{whereStr} '{str(id)}' ,"
            updateNum  = updateNum+1
            if updateNum >= 5000:
                updateDbSql = f"{updateSql} and {whereInCloumn} in ( {whereStr[:-1]} )"
                print(updateDbSql)
                curConf.execute(updateDbSql)
                print(f'---------------成功执行---------------')
                connConf.commit()
                updateNum = 0
                whereStr= ""
        if updateNum > 0:
            updateDbSql = f"{updateSql} and {whereInCloumn} in ( {whereStr[:-1]} )"
            print(updateDbSql)
            curConf.execute(updateDbSql)
            print(f'---------------{logDetail}:执行完毕---------------')
            connConf.commit()
    except Exception as e:
        print(e)
    finally:
        connData.close()
        curData.close()
        connConf.close()
        curConf.close()



#如 2022年12月13号 获取 221213 作为分表后缀
def getdateyyMMdd(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y%m%d')
    re_date = re_date[2:]
    return re_date

#早间服务执行一次
def zjkjExecute():
    selectSql = f"""select DISTINCT(t.active_id) from log_common_service_{yyMMddDate} t where t.key ='morningCallStart' """
    updateSql = "update life_assistant_devices t set  switches_state1 = switches_state1 |(1 << (11 - 1)) where 1=1"
    whereInCloumn = "active_id"
    logDetail = "早间服务执行一次"
    updateLogUtils(selectSql,updateSql,whereInCloumn,logDetail)

    selectSql = f"""select DISTINCT(t.active_id) from log_common_service_{yyMMddDate} t where t.key ='openScreen' """
    updateSql = "update life_assistant_devices t set  switches_state1 = switches_state1 |(1 << (11 - 1)) where 1=1"
    whereInCloumn = "active_id"
    logDetail = "早间服务执行一次"
    updateLogUtils(selectSql,updateSql,whereInCloumn,logDetail)

# 夜间音量执行一次
def yjylExecute():
    selectSql = f"""select DISTINCT(t.active_id) from log_common_service_{yyMMddDate} t where t.key ='narvExecute' """
    updateSql = "update life_assistant_devices t set  switches_state1 = switches_state1 |(1 << (12 - 1)) where 1=1"
    whereInCloumn = "active_id"
    logDetail = "夜间音量执行一次"
    updateLogUtils(selectSql,updateSql,whereInCloumn,logDetail)

def sjtxExecute():
    selectSql = f"""select DISTINCT(t.active_id) from log_common_service_{yyMMddDate} t where t.key ='srShowResult' """
    updateSql = "update life_assistant_devices t set  switches_state1 = switches_state1 |(1 << (15 - 1)) where 1=1"
    whereInCloumn = "active_id"
    logDetail = "睡觉提醒执行一次"
    updateLogUtils(selectSql,updateSql,whereInCloumn,logDetail)

#助眠功能执行一次
def zmgnExecute():
    selectSql = f"""select DISTINCT(t.active_id) from log_common_service_{yyMMddDate} t where t.key ='saEnterPage' """
    updateSql = "update life_assistant_devices t set  switches_state1 = switches_state1 |(1 << (16 - 1)) where 1=1"
    whereInCloumn = "active_id"
    logDetail = "助眠功能执行一次"
    updateLogUtils(selectSql,updateSql,whereInCloumn,logDetail)

#早间开机咨询弹窗
def zjkjAskShow():
    selectSql = f"""select DISTINCT(t.active_id)  from log_common_service_{yyMMddDate} t where t.key ='askShow' and JSON_EXTRACT(t.msg,'$.result') =1 and  (JSON_EXTRACT(t.msg,'$.service') is null or JSON_EXTRACT(t.msg,'$.service') ='ZJKJ') """
    updateSql = " update life_assistant_devices t set zjkj_ask_num = zjkj_ask_num+1  "
    whereInCloumn = "active_id"
    logDetail = "早间开机咨询弹窗,计数"
    updateLogUtils(selectSql,updateSql,whereInCloumn,logDetail)
    updateSql = " update service_push_list t set t.push_status = 4 ,last_update_time = now() where 1=1 and (select tt.service_id from service_push_task tt where tt.id = t.task_id ) =  3 and push_status!=4"
    logDetail = "早间开机咨询弹窗,设置推送状态"
    updateLogUtils(selectSql,updateSql,whereInCloumn,logDetail)


#睡觉提醒咨询弹窗
def sjtxAskShow():
    selectSql = f"""select DISTINCT(t.active_id)  from log_common_service_{yyMMddDate} t where t.key ='askShow' and JSON_EXTRACT(t.msg,'$.result') =1 and  JSON_EXTRACT(t.msg,'$.service') ='SJTX' """
    updateSql = " update life_assistant_devices t set sjtx_ask_num = sjtx_ask_num+1 "
    whereInCloumn = "active_id"
    logDetail = "睡觉提醒咨询弹窗,计数"
    updateLogUtils(selectSql,updateSql,whereInCloumn,logDetail)
    updateSql = " update service_push_list t set t.push_status = 4 ,last_update_time = now() where 1=1 and (select tt.service_id from service_push_task tt where tt.id = t.task_id ) =  5 and push_status!=4"
    whereInCloumn = "active_id"
    logDetail = "睡觉提醒咨询弹窗,设置推送状态"
    updateLogUtils(selectSql,updateSql,whereInCloumn,logDetail)


def zjkjOpenTime():
    selectSql = f"""select DISTINCT(t.active_id)  from log_common_service_230530 t where t.key ='openServer2' and  JSON_EXTRACT(t.msg,'$.serId') ='SJTX'"""
    updateSql = "update life_assistant_devices t set t.sjtx_open_time = now() , last_update_time = now() where 1=1 and sjtx_open_time is null"
    whereInCloumn = "active_id"
    logDetail = "助眠功能执行一次"
    updateLogUtils(selectSql,updateSql,whereInCloumn,logDetail)

if __name__ == '__main__':
    d = 1
    yyMMddDate = getdateyyMMdd(d)
    zjkjExecute()
    yjylExecute()
    sjtxExecute()
    zmgnExecute()
    #zjkjAskShow()
    #sjtxAskShow()